module.exports = {
  test(data) {
    // db.connect("SELECT	base_url.url FROM	base_url WHERE	base_url.id = 1", data, fun)
    // return db.connect("SELECT	* FROM	books WHERE	id = '10090'")
    return db.connect(`SELECT	*FROM	imgs WHERE  imgs.chapter_id = '106246_109';`)
  },


  /* 
    Books 表 
  */
  // 解析列表里面的漫画路径数据，并保存到数据库
  addBook(data) {
    // 一次性添加多条数据
    return db.connect(`INSERT INTO books ( id, url, base_url_id) VALUES ${data[0]} ON DUPLICATE KEY UPDATE url =VALUES	( url )`)
  },
  // 更新book信息，使用插入的方式，不适应 UPDATE 关键字，UPDATE 的方式效率慢
  updateBook2(data) {
    return db.connect(`INSERT INTO books (id, name_, author, status_, new_chapter, type, region, is_updata_info, img, tag, update_date) VALUES (${data[0]}) ON DUPLICATE KEY UPDATE name_ =VALUES( name_ ),author =VALUES( author ),status_ =VALUES( status_ ),new_chapter =VALUES( new_chapter ),type =VALUES( type ),region =VALUES( region ),is_updata_info =VALUES( is_updata_info ),img =VALUES( img ),tag =VALUES( tag ),update_date =VALUES( update_date )`)
  },
  // 更新book信息
  updateBook(data) {
    return db.connect(`UPDATE books SET ${data[0]} WHERE id = ${data[1]};`)
  },
  // 获取books表中对应id的数据
  getBook(data = []) { // offset = N 跳过结果集中的前 N 行, limit = Y 指定要返回的行数Y
    return db.connect(`SELECT	* FROM	books INNER JOIN chapter ON books.id = chapter.book_id WHERE books.id=?`, data)
  },
  // 获取books表中n条数据
  getBooksLimit(offset = 0, limit = 10) { // offset = N 跳过结果集中的前 N 行, limit = Y 指定要返回的行数Y
    return db.connect(`SELECT	* FROM	books INNER JOIN base_url ON books.base_url_id = base_url.base_url_id LIMIT ?,?;`, [offset, limit])
  },
  // 获取books表中n条数据，有条件的查询因素，比如需要类型、标签等
  getBooksFactorLimit(chapter_count = 0, sql = '', offset = 0, limit = 10) { // sql 为需要查询的条件， offset = N 跳过结果集中的前 N 行, limit = Y 指定要返回的行数Y
    if (!chapter_count)
      s = `SELECT	* FROM	books INNER JOIN base_url ON books.base_url_id = base_url.base_url_id ${sql} LIMIT ?,?;`
    else
      s = `
        SELECT b.id, b.name_, b.img, c_count.chapter_count 
        FROM books b 
        INNER JOIN (
            SELECT book_id, COUNT(*) as chapter_count
            FROM chapter 
            GROUP BY book_id
            HAVING COUNT(*) > ${chapter_count}
        ) c_count ON b.id = c_count.book_id
        INNER JOIN base_url bu ON b.base_url_id = bu.base_url_id
        ${sql}
        LIMIT ?,?;
      `
    // let s = `
    //   SELECT b.id, b.name_, b.img 
    //   FROM books b 
    //   INNER JOIN (
    //       SELECT book_id, COUNT(*) as chapter_count
    //       FROM chapter 
    //       GROUP BY book_id
    //       HAVING COUNT(*) > 20
    //   ) c_count ON b.id = c_count.book_id
    //   INNER JOIN base_url bu ON b.base_url_id = bu.base_url_id
    //   WHERE b.name_ IS NOT NULL
    //   ORDER BY b.update_date DESC
    //   LIMIT 0, 11;`
    console.log(`&&&&&==-==  `, s)
    return db.connect(s, [offset, limit])
  },
  // 获取books表中还未更新book信息的前n条数据
  getBooksFormIs_updata_infoLimit(offset = 0, limit = 10) { // offset = N 跳过结果集中的前 N 行, limit = Y 指定要返回的行数Y
    return db.connect(`SELECT	* FROM	books INNER JOIN base_url ON books.base_url_id = base_url.base_url_id  WHERE	is_updata_info IS NULL OR is_updata_info=0 OR update_date IS NULL LIMIT ?,?;`, [offset, limit])
  },
  // 获取books表中半年内连载的
  getBooksForm_6_MONTH() {
    return db.connect(`SELECT * FROM books INNER JOIN base_url ON books.base_url_id = base_url.base_url_id WHERE update_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND status_ = 0 AND name_ IS NOT NULL AND name_ != '' ORDER BY update_date DESC;`)
  },
  // 查询执行id的book是否存在
  getBookIsExist(data) {
    return db.connect(`SELECT	* FROM	books WHERE id=?`, [data])
  },


  /* 
    tags 表
  */
  // 添加tags标签,一次性添加多条数据
  addTags(data) {
    return db.connect(`INSERT INTO tags (tag_id, tag_title) VALUES ${data[0]} ON DUPLICATE KEY UPDATE tag_title =VALUES	( tag_title ),tag_id =VALUES	( tag_id )`)
  },
  // 获取tags标签
  getTags() {
    return db.connect(`SELECT	* FROM	tags`);
  },
  // 获取tags标签表中还未搜索的前n条数据
  getTagsFormIs_searchLimit(offset = 0, limit = 200) { // offset = N 跳过结果集中的前 N 行, limit = Y 指定要返回的行数Y
    return db.connect(`SELECT	* FROM	tags WHERE	is_search IS NULL OR is_search=0 LIMIT ?,?;`, [offset, limit])
  },
  // 更新tags标签
  updateTag(data) {
    return db.connect(`UPDATE tags SET is_search=1 WHERE tag_id = ?;`, data);
  },


  /* 
    chapter 表
  */
  // 添加章节信息,一次性添加多条数据
  addChapter(data) {
    return db.connect(`INSERT INTO chapter (id, book_id, chapter_index, chapter_url, chapter_title, base_url_id ) VALUES ${data[0]} ON DUPLICATE KEY UPDATE book_id =VALUES( book_id ),chapter_index =VALUES( chapter_index ),chapter_url =VALUES( chapter_url ),chapter_title =VALUES( chapter_title ),base_url_id =VALUES( base_url_id )`)
  },
  // 更新chapter表的is_save_img，表示图片已经保存
  updateChapter_is_save_img(data) {
    return db.connect(`INSERT INTO chapter (id, is_save_img ) VALUES (?,?) ON DUPLICATE KEY UPDATE is_save_img =VALUES( is_save_img )`, data)
  },
  // // 获取chapter表中对应book_id的数据
  // getChapter(book_id) {
  //   return db.connect(`SELECT	* FROM	chapter INNER JOIN base_url ON chapter.base_url_id = base_url.base_url_id WHERE	is_save_img IS NULL OR is_save_img=0 LIMIT ?,?;`, book_id)
  // },
  // 获取chapter表中还未更新is_save_img信息的前n条数据
  getChapterFormIs_save_img(offset = 0, limit = 10) { // offset = N 跳过结果集中的前 N 行, limit = Y 指定要返回的行数Y
    return db.connect(`SELECT	* FROM	chapter INNER JOIN base_url ON chapter.base_url_id = base_url.base_url_id WHERE	is_save_img IS NULL OR is_save_img=0 LIMIT ?,?;`, [offset, limit])
  },


  /* 
    imgs 表
  */
  // 添加图片信息,一次性添加多条数据
  addImgs(data) {
    return db.connect(`INSERT INTO imgs (id, imgs_index, imgs_url, chapter_id, base_url_id ) VALUES ${data[0]} ON DUPLICATE KEY UPDATE imgs_index =VALUES( imgs_index ),imgs_url =VALUES( imgs_url ),chapter_id =VALUES( chapter_id ),base_url_id =VALUES( base_url_id )`)
  },
  // 获取Imgs表中chapter_id对应的数据
  getImgs(data = []) { // limit = Y 指定要返回的行数Y
    return db.connect(`SELECT * FROM imgs INNER JOIN base_url ON imgs.base_url_id = base_url.base_url_id WHERE  imgs.chapter_id = ?;`, data)
  },
  // 获取Imgs表中还未缩短url的前n条数据
  getImgsFormSmall(offset = 0, limit = 10) { // limit = Y 指定要返回的行数Y
    return db.connect(`SELECT * FROM imgs WHERE  imgs.base_url_id = 2 LIMIT ?,?;`, [offset, limit])
  },
  // 更新imgs表的某条数据
  updateImg(data) {
    return db.connect(`UPDATE imgs SET imgs_url = ?, base_url_id = ? WHERE id = ?;`, data)
  },


  /* 
    base_url 表
  */
  // 获取Base_url表的数据
  getBase_url() {
    return db.connect(`SELECT * FROM base_url`);
  },
  // 更改基础url
  updateBase_url(data) {
    return db.connect(`UPDATE base_url SET base_url = ? WHERE base_url_id = ?;`, data);
  }
}